const { Sequelize } = require('sequelize');

const sequelize = new Sequelize({
  dialect: 'mysql',
  host: 'localhost',
  port: 3306,
  username: 'root',
  password: '888mei525',
  database: 'lingxi_system',
  logging: false
});

async function createIncomingInvoicesTable() {
  try {
    await sequelize.authenticate();
    console.log('数据库连接成功');
    
    // 创建进项发票表
    await sequelize.query(`
      CREATE TABLE IF NOT EXISTS incoming_invoices (
        id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
        invoice_number VARCHAR(50) NOT NULL COMMENT '发票号码',
        invoice_code VARCHAR(50) COMMENT '发票代码',
        invoice_type_code VARCHAR(10) COMMENT '发票类型代码',
        invoice_kind_code VARCHAR(10) COMMENT '发票种类代码',
        invoice_status_code VARCHAR(10) COMMENT '发票状态代码',
        is_red_invoice VARCHAR(1) COMMENT '是否红字发票(Y/N)',
        seller_tax_number VARCHAR(50) COMMENT '销方识别号',
        seller_name VARCHAR(200) COMMENT '销方名称',
        buyer_tax_number VARCHAR(50) COMMENT '购方识别号',
        buyer_name VARCHAR(200) COMMENT '购方名称',
        invoice_date DATETIME COMMENT '开票日期',
        total_amount DECIMAL(15,2) COMMENT '金额',
        tax_amount DECIMAL(15,2) COMMENT '税额',
        total_tax_amount DECIMAL(15,2) COMMENT '价税合计',
        invoice_source VARCHAR(50) COMMENT '发票来源',
        issuer VARCHAR(50) COMMENT '开票人',
        tax_bureau_code VARCHAR(20) COMMENT '税务机关代码',
        tax_bureau_name VARCHAR(100) COMMENT '税务机关名称',
        organization_id INT NOT NULL COMMENT '组织ID',
        supplier_id INT COMMENT '供应商ID',
        purchase_order_id INT COMMENT '采购订单ID',
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uk_invoice_number (invoice_number),
        KEY idx_seller_tax_number (seller_tax_number),
        KEY idx_buyer_tax_number (buyer_tax_number),
        KEY idx_invoice_date (invoice_date),
        KEY idx_supplier_id (supplier_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='进项发票表'
    `);
    
    console.log('进项发票表创建成功');
  } catch (error) {
    console.error('创建表时出错:', error.message);
  } finally {
    await sequelize.close();
  }
}

createIncomingInvoicesTable();